package it.orma.iridehr.repository.opportunita;

import it.orma.iridehr.entities.*;
import it.orma.iridehr.entities.azienda.Azienda;
import it.orma.iridehr.entities.azienda.ContattoAzienda;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class OpportunitaRepository{

	static Logger log = LogManager.getLogger(OpportunitaRepository.class);

	@PersistenceContext
	private EntityManager entityManager;

	public void salvaDatiOpportunita(Opportunita opp){
		if (opp.getIdOpportunita()==null){
			opp.setProgressivo(nextProgressivo());
			opp.setDataCreazione(new Date());	
			entityManager.persist(opp);
		} else {
			opp.setDataModifica(new Date());
			entityManager.merge(opp);
		}
		entityManager.detach(opp);
	}
	
	public Long nextProgressivo(){
		
		String nativeSql = "SELECT MAX(progressivo) from opportunita";
		BigInteger nextProgr = (BigInteger) entityManager.createNativeQuery(nativeSql).getSingleResult();
		entityManager.detach(nextProgr);
		if (nextProgr == null)
			nextProgr=new BigInteger("1");
		return nextProgr.longValue()+1;
	}
	
	public List<Opportunita> caricaOpportunita(int page,Integer idAzienda){
		
		String nativeSql = "SELECT *"+
				  " FROM (SELECT n.codice,"+
			      "         n.progressivo,"+
				  "			n.descrizione," +
				  "			n.id_Opportunita"+	
			      " FROM opportunita n "+
				  " WHERE n.id_azienda = ?"+
			      "  ORDER BY n.dt_chiusura ASC) view "+
			      " LIMIT "+(page-1)*10+", 10";
				
		List<Opportunita> list = new ArrayList<Opportunita>();
		List<Object[]> result = entityManager.createNativeQuery(nativeSql).setParameter(1,idAzienda).getResultList();
		Iterator<Object[]> resultIt = result.iterator();
		Azienda az = new Azienda();
		az.setIdAzienda(idAzienda);
		while(resultIt.hasNext()){
			Object[] record = resultIt.next();
			Opportunita opp = new Opportunita();
			opp.setCodice((String)record[0]);
			if (record[1]!=null)
				opp.setProgressivo(((BigInteger)record[1]).longValue());
			opp.setDescrizione((String)record[2]);
			opp.setIdOpportunita(((BigInteger)record[3]).longValue());
			opp.setAzienda(az);
			list.add(opp);
		}
		
		entityManager.detach(result);
		entityManager.detach(list);
		
		return list;
	}
	
	public Integer countOpportunita(Integer idAzienda){
		
		String nativeSql = "select count(*) from opportunita where id_Azienda = ?";
		Query query = entityManager.createNativeQuery(nativeSql).setParameter(1, idAzienda);
		return ((BigInteger) query.getSingleResult()).intValue();
	}

}

